/**
 * 
 */
package vl.scanfile;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import com.liferay.portal.kernel.dao.jdbc.DataAccess;

import vl.scanfile.ulti.TreeNode;
import vl.scanfile.vo.FtpVO;

/**
 * @author Chau
 *
 */
public class DatabaseSupporter {
	static void listDirectoryFromDB(Connection con, TreeNode<FtpVO> parent) throws IOException, SQLException {
	    java.sql.Statement st = null;
		
		try {
			con = DataAccess.getConnection();
			st = con.createStatement();

			String query = "";
			if(parent.data.getType().equals("root")) {
				query = "Select * from c_kho";
				java.sql.ResultSet rs = st.executeQuery(query);
				while (rs.next()) {
					String text = rs.getString("ten_kho");
					String type = "kho";
					int ma = rs.getInt("ma_kho");
					FtpVO vo = new FtpVO();
					vo.setText(text);
					vo.setType(type);
					vo.setMa(ma);
					TreeNode<FtpVO> node = new TreeNode<FtpVO>(vo);
					listDirectoryFromDB(con, node);
	                parent.addChild(node);
				}
			}
			else if(parent.data.getType().equals("kho")) {
				query = "Select * from c_tu where ma_kho="+parent.data.getMa();
				java.sql.ResultSet rs = st.executeQuery(query);
				while (rs.next()) {
					String text = rs.getString("ten_tu");
					String type = "tu";
					int ma = rs.getInt("ma_tu");
					FtpVO vo = new FtpVO();
					vo.setText(text);
					vo.setType(type);
					vo.setMa(ma);
					TreeNode<FtpVO> node = new TreeNode<FtpVO>(vo);
					listDirectoryFromDB(con, node);
	                parent.addChild(node);
				}
			}
			else if(parent.data.getType().equals("tu")) {
				query = "Select * from c_ke where ma_tu="+parent.data.getMa();
				java.sql.ResultSet rs = st.executeQuery(query);
				while (rs.next()) {
					String text = rs.getString("ten_ke");
					String type = "ke";
					int ma = rs.getInt("ma_ke");
					FtpVO vo = new FtpVO();
					vo.setText(text);
					vo.setType(type);
					vo.setMa(ma);
					TreeNode<FtpVO> node = new TreeNode<FtpVO>(vo);
					listDirectoryFromDB(con, node);
	                parent.addChild(node);
				}
			}
			else if(parent.data.getType().equals("ke")) {
				query = "Select * from c_hop where ma_ke="+parent.data.getMa();
				java.sql.ResultSet rs = st.executeQuery(query);
				while (rs.next()) {
					String text = rs.getString("ten_hop");
					String type = "hop";
					int ma = rs.getInt("ma_hop");
					FtpVO vo = new FtpVO();
					vo.setText(text);
					vo.setType(type);
					vo.setMa(ma);
					TreeNode<FtpVO> node = new TreeNode<FtpVO>(vo);
					listDirectoryFromDB(con, node);
	                parent.addChild(node);
				}
			}
			else if(parent.data.getType().equals("hop")) {
				query = "Select * from c_hoso l1 left join c_loaihoso l2 on l1.loai_hoso = l2.ma_hoso where ma_hop="+parent.data.getMa();
				java.sql.ResultSet rs = st.executeQuery(query);
				while (rs.next()) {
					String text = rs.getString("ten_hoso");
					String type = "file";
					int ma = rs.getInt("ma_hoso");
					FtpVO vo = new FtpVO();
					vo.setText(text);
					vo.setType(type);
					vo.setMa(ma);
					TreeNode<FtpVO> node = new TreeNode<FtpVO>(vo);
					listDirectoryFromDB(con, node);
	                parent.addChild(node);
				}
			}
		}
		catch (Exception e) {
		} 
		finally {
			st.close();
			con.close();
		}
	}
	static int createKho(Connection con, String ten_kho) throws SQLException {
	    java.sql.Statement st = null;
		int id = -1;
		
		try {
			con = DataAccess.getConnection();
			st = con.createStatement();
			String query1 = "SELECT MAX(pr_key) as pr_key FROM c_kho";
			java.sql.ResultSet rs1 = st.executeQuery(query1);
			while (rs1.next()) {
				id = rs1.getInt("pr_key");
			}
			id = id + 1;
			String query = "";
			query = "Insert into c_kho(ten_kho, ma_kho,pr_key) Values('"+ten_kho+"', "+id+", "+id+")";
			st.executeQuery(query);
		}
		catch (Exception e) {
		} 
		finally {
			st.close();
			con.close();
		}
		return id;
	}
	static int createTu(Connection con, String ten_tu, int ma_kho) throws SQLException {
	    java.sql.Statement st = null;
		int id = -1;
		
		try {
			con = DataAccess.getConnection();
			st = con.createStatement();
			String query1 = "SELECT MAX(pr_key) as pr_key FROM c_tu";
			java.sql.ResultSet rs1 = st.executeQuery(query1);
			while (rs1.next()) {
				id = rs1.getInt("pr_key");
			}
			id = id + 1;
			String query = "";
			query = "Insert into c_kho(ten_tu, ma_tu, pr_key, ma_kho) Values('"+ten_tu+"', "+id+", "+id+", "+ma_kho+")";
			st.executeQuery(query);
		}
		catch (Exception e) {
		} 
		finally {
			st.close();
			con.close();
		}
		return id;
	}
	static int createKe(Connection con, String ten_ke, int ma_tu) throws SQLException {
	    java.sql.Statement st = null;
		int id = -1;
		
		try {
			con = DataAccess.getConnection();
			st = con.createStatement();
			String query1 = "SELECT MAX(pr_key) as pr_key FROM c_ke";
			java.sql.ResultSet rs1 = st.executeQuery(query1);
			while (rs1.next()) {
				id = rs1.getInt("pr_key");
			}
			id = id + 1;
			String query = "";
			query = "Insert into c_kho(ten_ke, ma_ke, pr_key, ma_tu) Values('"+ten_ke+"', "+id+", "+id+", "+ma_tu+")";
			st.executeQuery(query);
		}
		catch (Exception e) {
		} 
		finally {
			st.close();
			con.close();
		}
		return id;
	}
	static int createHop(Connection con, String ten_hop, int ma_ke) throws SQLException {
	    java.sql.Statement st = null;
		int id = -1;
		
		try {
			con = DataAccess.getConnection();
			st = con.createStatement();
			String query1 = "SELECT MAX(pr_key) as pr_key FROM c_hop";
			java.sql.ResultSet rs1 = st.executeQuery(query1);
			while (rs1.next()) {
				id = rs1.getInt("pr_key");
			}
			id = id + 1;
			String query = "";
			query = "Insert into c_kho(ten_hop, ma_hop, pr_key, ma_ke) Values('"+ten_hop+"', "+id+", "+id+", "+ma_ke+")";
			st.executeQuery(query);
		}
		catch (Exception e) {
		} 
		finally {
			st.close();
			con.close();
		}
		return id;
	}
	static void createHoSo(Connection con, int ma_hoso, int ma_hop, String ten_hoso, String duongDan) throws SQLException {
	    java.sql.Statement st = null;
		
		try {
			con = DataAccess.getConnection();
			st = con.createStatement();
			
			String query = "";
			query = "Insert into c_kho(loai_hoso, ma_hop) Values('"+ma_hoso+"', "+ma_hop+")";
			st.executeQuery(query);
		}
		catch (Exception e) {
		} 
		finally {
			st.close();
			con.close();
		}
	}
}
